package com.jj.dao.impl;

import com.jj.dao.BaseDao;
import com.jj.dao.StatisticsDao;
import com.jj.entity.Department;
import com.jj.entity.Employee;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class StatisticsDaoImpl extends BaseDao implements StatisticsDao {
    @Override
    public int getTotalEmployees() {
        String sql = "SELECT COUNT(*) FROM employee";
        return getCount(sql);
    }

    @Override
    public int getTotalDepartments() {
        String sql = "SELECT COUNT(*) FROM department";
        return getCount(sql);
    }

    @Override
    public int getThisMonthHires() {
        String sql = "SELECT COUNT(*) FROM employee WHERE MONTH(onboarding) = MONTH(CURRENT_DATE()) " +
                "AND YEAR(onboarding) = YEAR(CURRENT_DATE())";
        return getCount(sql);
    }

    @Override
    public Map<Department, Integer> getDepartmentStats() {
        Map<Department, Integer> stats = new HashMap<>();
        String sql = "SELECT d.id, d.dept_name, d.description, COUNT(e.id) as employee_count " +
                "FROM department d LEFT JOIN employee e ON d.id = e.dept_id " +
                "GROUP BY d.id, d.dept_name, d.description";

        ResultSet rs = executeQuery(sql);
        try {
            while (rs != null && rs.next()) {
                Department dept = new Department();
                dept.setId(rs.getInt("id"));
                dept.setDeptName(rs.getString("dept_name"));
                dept.setDescription(rs.getString("description"));

                int count = rs.getInt("employee_count");
                stats.put(dept, count);
            }
        } catch (SQLException e) {
            throw new RuntimeException("获取部门统计信息失败", e);
        } finally {
            closedAll(rs);
        }
        return stats;
    }

    @Override
    public Map<String, Integer> getPositionStats() {
        Map<String, Integer> stats = new LinkedHashMap<>();
        String sql = "SELECT position, COUNT(*) as count FROM employee GROUP BY position ORDER BY count DESC";

        ResultSet rs = executeQuery(sql);
        try {
            while (rs != null && rs.next()) {
                stats.put(rs.getString("position"), rs.getInt("count"));
            }
        } catch (SQLException e) {
            throw new RuntimeException("获取职位统计信息失败", e);
        } finally {
            closedAll(rs);
        }
        return stats;
    }

    @Override
    public List<Employee> getRecentEmployees(int limit) {
        List<Employee> employees = new ArrayList<>();
        String sql = "SELECT e.*, d.dept_name, d.description " +
                "FROM employee e LEFT JOIN department d ON e.dept_id = d.id " +
                "ORDER BY e.onboarding DESC LIMIT ?";

        ResultSet rs = executeQuery(sql, limit);
        try {
            while (rs != null && rs.next()) {
                Employee emp = new Employee();
                emp.setId(rs.getInt("id"));
                emp.setEmpName(rs.getString("emp_name"));
                emp.setDeptId(rs.getInt("dept_id"));
                emp.setPosition(rs.getString("position"));
                emp.setOnboarding(rs.getDate("onboarding"));
                emp.setPhone(rs.getString("phone"));
                emp.setImgUrl(rs.getString("img_url"));

                Department dept = new Department();
                dept.setId(rs.getInt("dept_id"));
                dept.setDeptName(rs.getString("dept_name"));
                dept.setDescription(rs.getString("description"));
                emp.setDepartment(dept);

                employees.add(emp);
            }
        } catch (SQLException e) {
            throw new RuntimeException("获取最近入职员工失败", e);
        } finally {
            closedAll(rs);
        }
        return employees;
    }

    private int getCount(String sql) {
        ResultSet rs = executeQuery(sql);
        try {
            if (rs != null && rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException("获取统计数量失败", e);
        } finally {
            closedAll(rs);
        }
        return 0;
    }

}
